package com.fzk.blog.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.fzk.blog.bean.User;
import com.fzk.blog.bean.vo.UserInfo;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

/**
 * @author fzk
 * @date 2021-08-15 15:50
 */
public interface UserMapper extends BaseMapper<User> {
    @Select("select * from tbl_user where phone=#{phone} and flag=0")
    User getUserByPhone(String phone);

    @Select("select * from tbl_user where email=#{email} and flag=0")
    User getUserByEmail(String email);

    @Select("SELECT u.id,u.phone,u.username,r.name as roleName,u.head_portrait_url as headPortraitUrl,u.address,u.sex,u.email,u.email_flag emailFlag" +
            "   FROM tbl_user u LEFT JOIN tbl_role r ON (u.role_id=r.id AND r.flag=0) " +
            "   WHERE u.id=#{id} AND u.flag=0 ")
    UserInfo getUserInfoById(int id);

    @Select("SELECT u.id,u.phone,u.username,r.name as roleName,u.head_portrait_url as headPortraitUrl,u.address,u.sex,u.email,u.email_flag emailFlag" +
            "   FROM tbl_user u LEFT JOIN tbl_role r ON (u.role_id=r.id AND r.flag=0) " +
            "   WHERE u.flag=0 " +
            "   ORDER BY r.id")
    List<UserInfo> getUserInfoList();

    @Select("SELECT u.id,u.phone,u.username,r.name as roleName,u.head_portrait_url as headPortraitUrl,u.address,u.sex,u.email,u.email_flag emailFlag," +
            "   COUNT(b.id) as blogNumber " +
            "   FROM tbl_user u LEFT JOIN tbl_role r ON (u.role_id=r.id AND r.flag=0) " +
            "   LEFT JOIN tbl_blog b ON (u.id=b.create_by AND b.flag=0 AND b.published=TRUE) " +
            "   WHERE u.flag=0 " +
            "   GROUP BY u.id " +
            "   HAVING u.phone like '%' #{phone} '%' " +
            "   AND u.username like '%' #{username} '%' " +
            "   AND blogNumber>=#{blogNumberMin} " +
            "   AND blogNumber<=#{blogNumberMax} " +
            "   ORDER BY r.id,blogNumber DESC " +
            "   limit #{index},#{pageSize}")
    List<UserInfo> getUserInfoListPage(String phone, String username, int blogNumberMin, int blogNumberMax, int index, int pageSize);

    @Select("SELECT u.id,u.phone,u.username,r.name as roleName,u.head_portrait_url as headPortraitUrl,u.address,u.sex,u.email,u.email_flag emailFlag," +
            "   COUNT(b.id) as blogNumber " +
            "   FROM tbl_user u LEFT JOIN tbl_role r ON (u.role_id=r.id AND r.flag=0) " +
            "   LEFT JOIN tbl_blog b ON (u.id=b.create_by AND b.flag=0 AND b.published=TRUE) " +
            "   WHERE u.flag=0 " +
            "   GROUP BY u.id " +
            "   HAVING u.phone like '%' #{phone} '%' " +
            "   AND u.username like '%' #{username} '%' " +
            "   AND blogNumber>=#{blogNumberMin} " +
            "   AND blogNumber<=#{blogNumberMax} ")
    List<UserInfo> getUserInfoListTotal(String phone, String username, int blogNumberMin, int blogNumberMax);

    @Select("SELECT u.id,u.phone,u.username,u.password,r.name as roleName,u.head_portrait_url as headPortraitUrl,u.address,u.sex,u.email,u.email_flag emailFlag," +
            "   COUNT(b.id) as blogNumber,COUNT(c.id) as commentNumber" +
            "   FROM tbl_user u LEFT JOIN tbl_role r ON (u.role_id=r.id AND r.flag=0) " +
            "   LEFT JOIN tbl_blog b ON (u.id=b.create_by AND b.flag=0) " +
            "   LEFT JOIN tbl_comment c ON (u.id=c.user_id AND c.flag=0) " +
            "   WHERE u.id=#{id} AND u.flag=0 " +
            "   GROUP BY u.id")
    UserInfo getUserInfoWithPwd(int id);

    @Update("UPDATE tbl_user SET password=#{newPassword} WHERE id=#{loginId} AND flag=0 AND password!=#{newPassword} ")
    int resetPwd(int loginId, String newPassword);
}
